package jsyf.oa.dao.report;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository
public class LeaveReportDao extends BaseDao {

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return null;
    }


    /**
     * 获取当日集团所有请假信息
     *
     * @param
     * @return
     */
    public List<JSONObject> getleaveListByDateTime() {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT IFNULL(COUNT(1),0) leaveDaysCount, LEFT(aleave.BEGIN_TIME,10) beginTime, LEFT(aleave.END_TIME,10) endTime, aleave.REMARK remark, person.id userId, person.`name` userName,person.roleName, person.companyId, person.companyName, person.jobState \n");
        sb.append(" from t_sys_leave  AS aleave \n");
        sb.append(" LEFT JOIN v_sys_user AS person \n");
        sb.append(" ON person.id=aleave.USER_ID \n");
        sb.append(" WHERE \n");
        sb.append(" LEFT(BEGIN_TIME, 10)=LEFT(NOW(),10) \n");
        sb.append(" GROUP BY USER_ID,BEGIN_TIME \n");
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }


    public JSONObject getLeaveByCompanyId(String companyId, String isToday, String month) {

        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT IFNULL(COUNT(1),0) leaveCount, COMPANY_ID companyId FROM \n");
        sb.append(" (SELECT * from t_sys_leave WHERE 1=1 \n");
        if (isToday.equals("1")) {
            sb.append(" AND LEFT(BEGIN_TIME, 10)=LEFT(NOW(),10) \n");
        } else {
            sb.append(" AND LEFT(BEGIN_TIME, 7)=LEFT('" + month + "',7) \n");
        }
        if (companyId.length() != 0) {
            sb.append(" AND COMPANY_ID='" + companyId + "' \n");
        }
        sb.append(" GROUP BY USER_ID,BEGIN_TIME,COMPANY_ID) AS aleave \n");
        JSONObject jsonObject = super.queryForJsonList(sb.toString()).get(0);
        return jsonObject;
    }

    public List<JSONObject> getleaveListByCompanyId(String companyId, String isToday, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT IFNULL(COUNT(1),0) leaveDaysCount, LEFT(aleave.BEGIN_TIME,10) beginTime, LEFT(aleave.END_TIME,10) endTime, aleave.REMARK remark, person.id userId, person.`name` userName,person.roleName, person.companyId, person.companyName, person.jobState \n");
        sb.append(" from t_sys_leave  AS aleave \n");
        sb.append(" LEFT JOIN v_sys_user AS person \n");
        sb.append(" ON person.id=aleave.USER_ID \n");
        sb.append(" WHERE COMPANY_ID='" + companyId + "' \n");
        if (isToday.equals("1")) {
            sb.append(" AND LEFT(BEGIN_TIME, 10)=LEFT(NOW(),10) \n");
        } else {
            sb.append(" AND LEFT(BEGIN_TIME, 7)=LEFT('" + month + "',7) \n");
        }
        sb.append(" GROUP BY USER_ID,BEGIN_TIME \n");

        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }


    public List<JSONObject> getleaveListByDepartId(String departId, String isToday, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT IFNULL(COUNT(1),0) leaveCount, LEFT(aleave.BEGIN_TIME,10) beginTime, LEFT(aleave.END_TIME,10) endTime,aleave.REMARK remark, person.id userId, person.name userName, person.roleName, person.departName,person.jobState \n");
        sb.append("from t_sys_leave  AS aleave \n");
        sb.append("left JOIN v_sys_user as person ON aleave.USER_ID=person.id \n");
        sb.append("WHERE \n");
        sb.append("person.departId='" + departId + "' \n");
        if (isToday.equals("1")) {
            sb.append(" AND LEFT(BEGIN_TIME, 10)=LEFT(NOW(),10) \n");
        } else {
            sb.append(" AND LEFT(BEGIN_TIME, 7)=LEFT('" + month + "',7) \n");
        }
        sb.append("GROUP BY beginTime,userId \n");

        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getSubordinateLeaveListByUserId(String userId, String isToday, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT IFNULL(COUNT(1),0) leaveCount, LEFT(aleave.BEGIN_TIME,10) beginTime, LEFT(aleave.END_TIME,10) endTime,aleave.REMARK remark, person.id userId, person.name userName, person.JOB_STATE jonState \n");
        sb.append("from t_sys_leave  AS aleave \n");
        sb.append("left JOIN \n");
        sb.append("(SELECT * from t_sys_user \n");
        sb.append("WHERE PARENT_ID='" + userId + "' OR ID='" + userId + "') as person \n");
        sb.append("ON aleave.USER_ID=person.id \n");
        if (isToday.equals("1")) {
            sb.append(" WHERE LEFT(BEGIN_TIME, 10)=LEFT(NOW(),10) \n");
        } else {
            sb.append(" WHERE LEFT(BEGIN_TIME, 7)=LEFT('" + month + "',7) \n");
        }
        sb.append("AND person.id IS NOT NULL \n");
        sb.append("GROUP BY beginTime,userId \n");
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getLeaveListByUserId(String userId, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT LEFT(BEGIN_TIME,10) beginTime, LEFT(END_TIME,10) endTime, REMARK remark \n");
        sb.append("FROM t_sys_leave \n");
        sb.append("WHERE USER_ID='" + userId + "' \n");
        sb.append(" AND LEFT(BEGIN_TIME, 7)=LEFT('" + month + "',7) \n");
        sb.append("GROUP BY beginTime \n");
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }


    public List<JSONObject> getLeaveListBySupportId(String userId, String isToday, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT LEFT(aleave.BEGIN_TIME,10) beginTime, LEFT(aleave.END_TIME,10) endTime, aleave.REMARK remark, person.userId, person.userName, person.roleName, person.jobState \n");
        sb.append("from t_sys_leave AS aleave \n");
        sb.append("left JOIN \n");
        sb.append("((SELECT id userId, name userName, roleName,jobState FROM v_sys_user WHERE id='" + userId + "') \n");
        sb.append("UNION ALL \n");
        sb.append("(SELECT c.userid as userId, c.username as userName, d.roleName,,d.jobState from ( \n");
        sb.append("SELECT a.REGION_ID as regionId,REPLACE(b.`name`, '大区', '') as regionName,b.parent_id as parentId from t_sys_user_2_region as a \n");
        sb.append("left join t_sys_region as b on a.REGION_ID = b.id \n");
        sb.append("where a.user_id='" + userId + "' \n");
        sb.append("order by b.parent_id ASC,b.sort_no asc) as a \n");
        sb.append("LEFT JOIN t_sys_region as b on a.parentId=b.id \n");
        sb.append("LEFT JOIN v_region_manager as c on a.regionId=c.regionid \n");
        sb.append("LEFT JOIN v_sys_user as d on c.userid=d.id \n");
        sb.append("order by parent_id asc) \n");
        sb.append(") as person \n");
        sb.append("ON aleave.USER_ID=person.userId \n");
        if (isToday.equals("1")) {
            sb.append("WHERE LEFT(BEGIN_TIME, 10)=LEFT(NOW(),10) \n");
        } else {
            sb.append(" WHERE LEFT(BEGIN_TIME, 7)=LEFT('" + month + "',7) \n");
        }
        sb.append("AND person.userId is not null \n");
        sb.append("GROUP BY beginTime,userId \n");
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    /**
     * 获取请假报表-卡片信息
     *
     * @param userId
     * @param departId
     * @param companyId
     * @param groupId
     * @return
     */
    public List<JSONObject> getLeaveCount(String userId, String departId, String supportId, String superiorId, String companyId, String groupId) {
        StringBuffer sb = new StringBuffer();
        if (userId != null && userId.length() > 0) {
            sb.append("SELECT \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.beginTime,10)=LEFT(NOW(),10) THEN 1 ELSE 0 END),0) dayLeaveCount, \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.beginTime,7)=LEFT(NOW(),7) THEN 1 ELSE 0 END),0) monthLeaveCount \n");
            sb.append("FROM \n");
            sb.append("(SELECT LEFT(BEGIN_TIME,10) beginTime, LEFT(END_TIME,10) endTime, REMARK remark \n");
            sb.append("FROM t_sys_leave \n");
            sb.append("WHERE USER_ID='" + userId + "' \n");
            sb.append("GROUP BY beginTime) AS userLeave \n");
        } else if (superiorId != null && superiorId.length() > 0) {
            sb.append("SELECT \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.beginTime,10)=LEFT(NOW(),10) THEN 1 ELSE 0 END),0) dayLeaveCount,  \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.beginTime,7)=LEFT(NOW(),7) THEN 1 ELSE 0 END),0) monthLeaveCount \n");
            sb.append("FROM \n");
            sb.append("(SELECT IFNULL(COUNT(1),0) leaveCount, LEFT(aleave.BEGIN_TIME,10) beginTime, LEFT(aleave.END_TIME,10) endTime,aleave.REMARK remark, person.id userId, person.name userName \n");
            sb.append("from t_sys_leave  AS aleave \n");
            sb.append("left JOIN \n");
            sb.append("(SELECT * from t_sys_user \n");
            sb.append("WHERE PARENT_ID='" + superiorId + "') as person \n");
            sb.append("ON aleave.USER_ID=person.id \n");
            sb.append("WHERE person.id IS NOT NULL \n");
            sb.append("GROUP BY beginTime,userId) AS userLeave \n");
        } else if (departId != null && departId.length() > 0) {
            sb.append("SELECT \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.beginTime,10)=LEFT(NOW(),10) THEN 1 ELSE 0 END),0) dayLeaveCount,  \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.beginTime,7)=LEFT(NOW(),7) THEN 1 ELSE 0 END),0) monthLeaveCount \n");
            sb.append("FROM \n");
            sb.append("(SELECT LEFT(aleave.BEGIN_TIME,10) beginTime, LEFT(aleave.END_TIME,10) endTime,aleave.REMARK remark, person.id userId, person.name userName, person.roleName, person.departName \n");
            sb.append("from t_sys_leave  AS aleave \n");
            sb.append("left JOIN v_sys_user as person ON aleave.USER_ID=person.id \n");
            sb.append("WHERE \n");
            sb.append("person.departId='" + departId + "' \n");
            sb.append("GROUP BY beginTime,userId) AS userLeave \n");
        } else if (supportId != null && supportId.length() > 0) {
            sb.append("SELECT \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.beginTime,10)=LEFT(NOW(),10) THEN 1 ELSE 0 END),0) dayLeaveCount, \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.beginTime,7)=LEFT(NOW(),7) THEN 1 ELSE 0 END),0) monthLeaveCount \n");
            sb.append("FROM \n");
            sb.append("(SELECT LEFT(aleave.BEGIN_TIME,10) beginTime, LEFT(aleave.END_TIME,10) endTime, aleave.REMARK remark, person.userId, person.userName, person.roleName \n");
            sb.append("from t_sys_leave AS aleave \n");
            sb.append("left JOIN \n");
            sb.append("((SELECT id userId, name userName, roleName FROM v_sys_user WHERE id='" + supportId + "') \n");
            sb.append("UNION ALL \n");
            sb.append("(SELECT c.userid as userId, c.username as userName, d.roleName from ( \n");
            sb.append("SELECT a.REGION_ID as regionId,REPLACE(b.`name`, '大区', '') as regionName,b.parent_id as parentId from t_sys_user_2_region as a \n");
            sb.append("left join t_sys_region as b on a.REGION_ID = b.id \n");
            sb.append("where a.user_id='" + supportId + "' \n");
            sb.append("order by b.parent_id ASC,b.sort_no asc) as a \n");
            sb.append("LEFT JOIN t_sys_region as b on a.parentId=b.id \n");
            sb.append("LEFT JOIN v_region_manager as c on a.regionId=c.regionid \n");
            sb.append("LEFT JOIN v_sys_user as d on c.userid=d.id \n");
            sb.append("order by parent_id asc) \n");
            sb.append(") as person \n");
            sb.append("ON aleave.USER_ID=person.userId \n");
            sb.append("where person.userId is not null \n");
            sb.append("GROUP BY beginTime,userId) as userLeave \n");
        } else if (companyId != null && companyId.length() > 0) {
            sb.append("SELECT \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.BEGIN_TIME,10)=LEFT(NOW(),10) THEN 1 ELSE 0 END),0) dayLeaveCount, \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.BEGIN_TIME,7)=LEFT(NOW(),7) THEN 1 ELSE 0 END),0) monthLeaveCount \n");
            sb.append("FROM \n");
            sb.append("(SELECT * FROM t_sys_leave \n");
            sb.append("WHERE COMPANY_ID='" + companyId + "' \n");
            sb.append("GROUP BY USER_ID,BEGIN_TIME) AS userLeave \n");
        } else if (groupId != null && groupId.length() > 0) {
            sb.append("SELECT \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.BEGIN_TIME,10)=LEFT(NOW(),10) THEN 1 ELSE 0 END),0) dayLeaveCount, \n");
            sb.append("IFNULL(SUM(CASE WHEN LEFT(userLeave.BEGIN_TIME,7)=LEFT(NOW(),7) THEN 1 ELSE 0 END),0) monthLeaveCount \n");
            sb.append("FROM \n");
            sb.append("(SELECT * FROM t_sys_leave \n");
            sb.append("GROUP BY USER_ID,BEGIN_TIME) AS userLeave \n");
        }
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }


}
